package com.info.zhiduoduo.repository.mapper.partner;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.partner.PartnerAndCompanyRelationshipDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerCompanyExpireDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerCompanyInfoDto;
import com.info.zhiduoduo.repository.entity.partner.PartnerCompany;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 企业合伙人信息，保存佣金比例等信息 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-06-17
 */
public interface PartnerCompanyMapper extends BaseMapper<PartnerCompany> {

	@Select(" select COUNT(*) from zdd_partner_company " +
			" where partnerid = #{partnerid} " +
			" and compid = #{compid} " +
			" and type = #{type} " +
			" and deleted = 0")
	boolean existsByPartneridAndCompid(int partnerid, int compid, int type);

	@Select(" select pc.* from zdd_partner_company pc " +
			" where pc.compid = #{compid} " +
			" and if(#{type}=0, 1=1, pc.type=#{type}) " +
			" and pc.state = 1 " +
			" and pc.deleted = 0 ")
	List<PartnerCompany> getValidList(@Param("compid") int compid, @Param("type") int type);

	@Select(" select pc.compid,c.compname,pc.state " +
			" from zdd_partner_company pc " +
			" join zdd_company c on pc.compid=c.compid " +
			" where pc.partnerid=#{partnerid} group by pc.partnerid,pc.compid order by pc.id ")
	List<PartnerCompanyInfoDto> getPartnerCompanyBrokerageInfoByPartnerId(@Param("partnerid") int partnerid);

	@Select(" select * from zdd_partner_company " +
			" where partnerid = #{partnerid} " +
			" and deleted = 0 ")
	List<PartnerCompany> getPartnerCompanyByPartnerId(@Param("partnerid") int partnerid);

	@Select(" select pc.*,pa.realname,p.phone,pc.createtime as shareBenefitDate  " +
			" from zdd_partner_company pc " +
			" left join zdd_partner_account pa on pc.partnerid = pa.partnerid " +
			" left join zdd_partner p on pc.partnerid = p.partnerid " +
			" where pc.compid = #{compid}" +
			" and pc.deleted = 0 ")
	List<PartnerAndCompanyRelationshipDto> getPartnerCompanyByCompId(@Param("compid") int compid);

	@Select(" select COUNT(DISTINCT compid) from zdd_partner_company " +
			" where partnerid = #{partnerid} " +
			" and DATE(createtime) between #{start} and #{end} " +
			" and deleted = 0 ")
	int countPartnerCompanyByDate(@Param("partnerid") int partnerid, @Param("start")Date start, @Param("end") Date end);

	@Select(" select COUNT(DISTINCT compid) from zdd_partner_company " +
			" where partnerid = #{partnerid} " +
			" and YEAR(createtime) = YEAR(#{month}) " +
			" and QUARTER(createtime) = QUARTER(#{month}) " +
			" and deleted = 0 ")
	int countPartnerCompanyByQuarter(@Param("partnerid") int partnerid, @Param("month")Date month);


	@Select(" select DISTINCT compid from zdd_partner_company " +
			" where partnerid = #{partnerid} " +
			" and deleted = 0 ")
	List<Integer> getPartnerCompanyIds(@Param("partnerid") int partnerid);


	@Select(" select DISTINCT compid from zdd_partner_company " +
			" where partnerid = #{partnerid} " +
			" and expiredate >= CURDATE() " +
			" and DATE(createtime) between #{start} and #{end} " +
			" and deleted = 0 ")
	List<Integer> getPartnerCompanyIdsByDate(@Param("partnerid") int partnerid, @Param("start")Date start, @Param("end") Date end);

	@Select(" select pc.id,pa.partnerid,pa.realname,pc.compid,c.compname,p.phone,pa.idfrontimg,pa.idbackimg," +
			" pc.type,pc.settle_type,pc.ratio,pc.expiredate,pc.state,pc.createtime,bm.business_manager as partnerBusinessManager," +
			" bm1.business_manager as companyBusinessManager,pa.audit_pass_time as shareBenefitDate " +
			" from zdd_partner_company pc " +
			" join zdd_partner p on p.partnerid = pc.partnerid " +
			" join zdd_partner_account pa on pa.partnerid = p.partnerid " +
			" join zdd_company c on c.compid = pc.compid " +
			" join zdd_company_account ca on ca.compid = pc.compid " +
			" join zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
			" join zdd_business_manager bm1 on bm1.business_manager_id = ca.business_manager_id " +
			" where pc.expiredate between CURDATE() and DATE(#{date}) " +
			" and pc.deleted = 0 " +
			" order by pc.expiredate desc ")
	List<PartnerCompanyExpireDto> listPendingExpired(Date date);

	@Select(" select pc.id,pa.partnerid,pa.realname,pc.compid,c.compname,p.phone,pa.idfrontimg,pa.idbackimg," +
			" pc.type,pc.settle_type,pc.ratio,pc.expiredate,pc.state,pc.createtime,pa.audit_pass_time as shareBenefitDate " +
			" from zdd_partner_company pc " +
			" join zdd_partner p on p.partnerid = pc.partnerid " +
			" join zdd_partner_account pa on pa.partnerid = p.partnerid " +
			" join zdd_company c on c.compid = pc.compid " +
			" where IF(ISNULL(#{from}), 1=1, pc.expiredate >= DATE(#{from})) " +
			" AND IF(ISNULL(#{to}), 1=1, pc.expiredate <= DATE(#{to})) " +
			" AND IF(#{state} = 0, 1 = 1, pc.state = #{state}) " +
			" AND CONCAT(pa.realname, c.compname) like '%${name}%' " +
			" AND CASE WHEN #{expired}=1 THEN pc.expiredate > CURDATE() " +
			" WHEN #{expired}=2 THEN pc.expiredate <= CURDATE() " +
			" ELSE 1=1 " +
			" END " +
			" and pc.deleted = 0 " +
			" order by pc.expiredate desc ")
	List<PartnerCompanyExpireDto> listExpired(String name, int state, int expired, Date from, Date to);
}
